/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package giormi;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import javax.swing.JOptionPane;
import javax.swing.JTable;
/**
 *
 * @author giorgio
 */
public class searchstocks {
    
    private String input;
    private String searchbywhat;
    private JTable stable;
    
    public searchstocks(JTable stable){
        this.stable = stable;
    }
    public searchstocks(String input,String searchbywhat,JTable stable){
       this.input = input;
       this.searchbywhat = searchbywhat;
       this.stable = stable;
       
       if ("by Date Posted".equals(searchbywhat)){
        searchbydateposted();
       }
        else if ("by Product Name".equals(searchbywhat)){
        searchbypname();
        }
        
    }
    
    private Connection conn;
    private String SQLStatement;
    private String URL;
    private Statement stmt;
    public ResultSet rset;   
    
  public int gettablerows(){
        int total=0;
        connect();
        try {
            
               
                SQLStatement = "SELECT COUNT(idstock) from stock";

    
           
            rset = stmt.executeQuery(SQLStatement);
            while (rset.next()) {
                total = rset.getInt(1); // store result to total
            }
            rset.close();
            disconnect();
        } catch(Exception e){
            System.out.println(e.getMessage());
            JOptionPane.showMessageDialog(null,e.getMessage(),"ERROR", JOptionPane.WARNING_MESSAGE);
        }
        return total;
    }

    public void erase(){
        for (int i=0; i<gettablerows(); i++){ // row count
            stable.setValueAt("", i, 0);
            stable.setValueAt("", i, 1);
            stable.setValueAt("", i, 2);
            stable.setValueAt("", i, 3);
            stable.setValueAt("", i, 4);
            stable.setValueAt("", i, 5);
            stable.setValueAt("", i, 6);
            stable.setValueAt("", i, 7);
            stable.setValueAt("", i, 8);
            stable.setValueAt("", i, 9);
            stable.setValueAt("", i, 10);
            stable.setValueAt("", i, 11);
            stable.setValueAt("", i, 12);
            stable.setValueAt("", i, 13);
            stable.setValueAt("", i, 14);
            stable.setValueAt("", i, 15);
        }
    }   
public void connect(){
        try{
            //1
//            URL = "jdbc:odbc:introdb";
//            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
//            conn = DriverManager.getConnection(URL,"root","huhu");
            DBConnection db = new DBConnection();
            conn = DBConnection.get();
            //2
            stmt = conn.createStatement();
        }
        catch(Exception e){
            System.out.println(e.getMessage());
            JOptionPane.showMessageDialog(null,e.getMessage(),"ERROR", JOptionPane.WARNING_MESSAGE);
        }
    }

    public void disconnect(){
        try{
            //5
            stmt.close();
            conn.close();
        }
        catch(Exception ex){
            System.out.println(ex.getMessage());
            JOptionPane.showMessageDialog(null,ex.getMessage(),"ERROR", JOptionPane.WARNING_MESSAGE);
        }
        
        
    }
    public void searchbypname(){
        erase();
        connect();
        int i =0;
        
        try{
           SQLStatement = "SELECT p_name,p_packing,p_netweight,p_status,p_barcode,idstock,s_dateposted,s_datereceived,s_quantity,s_balance,s_rrnum,s_quedan,s_expdate,s_proddate,s_lotnum,s_notes FROM stock,product WHERE stock.s_idproduct = product.idproduct AND product.p_name LIKE '%" +input+"%'";
             rset = stmt.executeQuery(SQLStatement);
            while (rset.next()) {
                stable.setValueAt(rset.getString("idstock"), i, 0);
                stable.setValueAt(rset.getString("s_dateposted"), i, 1);
                stable.setValueAt(rset.getString("s_datereceived"), i, 2);
                stable.setValueAt(rset.getString("s_quantity"), i, 3);
                stable.setValueAt(rset.getString("s_balance"), i, 4);
                stable.setValueAt(rset.getString("p_name"), i, 5);
                stable.setValueAt(rset.getString("p_packing"), i, 6);
                stable.setValueAt(rset.getString("p_netweight"), i, 7);
                stable.setValueAt(rset.getString("p_status"), i, 8);
                stable.setValueAt(rset.getString("p_barcode"), i, 9);
                stable.setValueAt(rset.getString("s_quedan"), i, 10);
                stable.setValueAt(rset.getString("s_rrnum"), i, 11);
                stable.setValueAt(rset.getString("s_proddate"), i, 12);
                stable.setValueAt(rset.getString("s_expdate"), i, 13);
                stable.setValueAt(rset.getString("s_lotnum"), i, 14);
                stable.setValueAt(rset.getString("s_notes"), i, 15);
                i++;
            }
            rset.close();
            disconnect();
        }
        catch(Exception ex){
            System.out.println(ex.getMessage());
            JOptionPane.showMessageDialog(null,ex.getMessage(),"ERROR", JOptionPane.WARNING_MESSAGE);
            }
        }
    public void searchbydateposted(){
        erase();
        connect();
        int i =0;

        if (input.length()!=8)
            input = date.convertDate(input);

        try{
           SQLStatement = "SELECT idstock,s_dateposted,s_datereceived,s_quantity,s_balance,p_name,p_packing,p_netweight,p_status,p_barcode,s_rrnum,s_quedan,s_expdate,s_proddate,s_lotnum,s_notes FROM stock,product WHERE stock.s_idproduct = product.idproduct AND stock.s_dateposted = '"+input+ "'";
            rset = stmt.executeQuery(SQLStatement);
            while (rset.next()) {
                stable.setValueAt(rset.getString("idstock"), i, 0);
                stable.setValueAt(rset.getString("s_dateposted"), i, 1);
                stable.setValueAt(rset.getString("s_datereceived"), i, 2);
                stable.setValueAt(rset.getString("s_quantity"), i, 3);
                stable.setValueAt(rset.getString("s_balance"), i, 4);
                stable.setValueAt(rset.getString("p_name"), i, 5);
                stable.setValueAt(rset.getString("p_packing"), i, 6);
                stable.setValueAt(rset.getString("p_netweight"), i, 7);
                stable.setValueAt(rset.getString("p_status"), i, 8);
                stable.setValueAt(rset.getString("p_barcode"), i, 9);
                stable.setValueAt(rset.getString("s_quedan"), i, 10);
                stable.setValueAt(rset.getString("s_rrnum"), i, 11);
                stable.setValueAt(rset.getString("s_proddate"), i, 12);
                stable.setValueAt(rset.getString("s_expdate"), i, 13);
                stable.setValueAt(rset.getString("s_lotnum"), i, 14);
                stable.setValueAt(rset.getString("s_notes"), i, 15);
                i++;
            }
            rset.close();
            disconnect();
        }
        catch(Exception ex){
            System.out.println(ex.getMessage());
            JOptionPane.showMessageDialog(null,ex.getMessage(),"ERROR", JOptionPane.WARNING_MESSAGE);
            }
        }
    public void dwindlingstocks(){
        erase();
        connect();
        int i =0;
        
        try{
           SQLStatement = "SELECT stock.idstock, stock.s_dateposted, stock.s_datereceived, stock.s_quantity, stock.s_balance, product.p_name, product.p_packing,"
                         + "product.p_netweight, product.p_status, product.p_barcode, stock.s_quedan, stock.s_rrnum, stock.s_proddate, "
                         + "stock.s_expdate, stock.s_lotnum, stock.s_notes "
                         + "FROM stock, product "
                         + "WHERE stock.s_idproduct = product.idproduct "
                         + "AND stock.s_balance > 0 "
                         + "AND stock.s_balance <=10 "
                         + "ORDER BY idstock";

           rset = stmt.executeQuery(SQLStatement);
            while (rset.next()) {
                stable.setValueAt(rset.getString("idstock"), i, 0);
                stable.setValueAt(rset.getString("s_dateposted"), i, 1);
                stable.setValueAt(rset.getString("s_datereceived"), i, 2);
                stable.setValueAt(rset.getString("s_quantity"), i, 3);
                stable.setValueAt(rset.getString("s_balance"), i, 4);
                stable.setValueAt(rset.getString("p_name"), i, 5);
                stable.setValueAt(rset.getString("p_packing"), i, 6);
                stable.setValueAt(rset.getString("p_netweight"), i, 7);
                stable.setValueAt(rset.getString("p_status"), i, 8);
                stable.setValueAt(rset.getString("p_barcode"), i, 9);
                stable.setValueAt(rset.getString("s_quedan"), i, 10);
                stable.setValueAt(rset.getString("s_rrnum"), i, 11);
                stable.setValueAt(rset.getString("s_proddate"), i, 12);
                stable.setValueAt(rset.getString("s_expdate"), i, 13);
                stable.setValueAt(rset.getString("s_lotnum"), i, 14);
                stable.setValueAt(rset.getString("s_notes"), i, 15);
                i++;
            }
            rset.close();
            disconnect();
        }
        catch(Exception ex){
            System.out.println(ex.getMessage());
            JOptionPane.showMessageDialog(null,ex.getMessage(),"ERROR", JOptionPane.WARNING_MESSAGE);
            }
}
    }

